/**
 * Copyright (C), 2020, JieYi Software System Co., Ltd.
 * All rights reserved.
 * FileName:       InitMngTablesService.java
 *
 * @Description:
 * @author: feiwe
 * Modification History:
 * Date         Author        Version        Discription
 * -------------------------------------------------------
 * 2020/11/19      feiwe                         add
 */


package com.fengwei.service;

import cn.hutool.core.io.resource.ResourceUtil;
import com.fengwei.util.GsonUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;
import java.util.StringTokenizer;

@Service
public class InitMngTablesService {
    private final Logger logger = LoggerFactory.getLogger(InitMngTablesService.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private Environment env;

    private String[] tableNames = {"mdms_database", "mdms_datachange_apply", "mdms_sshserver", "mdms_sshserver_user", "mdms_user",
            "mdms_user_commonsql", "mdms_user_database", "mdms_user_log", "mdms_user_privilige", "mdms_user_sshserver", "mdms_ver"};

    public void initMngTables() throws Exception {
        String driverClassName = env.getProperty("spring.datasource.driver-class-name");
        switch (driverClassName) {
            case "com.mysql.cj.jdbc.Driver":
                for (String tableName : tableNames) {
                    createMysqlTableAndIndex(tableName);
                }
                break;
//            case "org.postgresql.Driver":
//                TODO:貌似pgsql里面下面这个在druid不支持创建函数，后面再说吧
//                final String functionSql = "create or replace function upd_timestamp() returns trigger as\n" +
//                        "$$\n" +
//                        "begin\n" +
//                        "    new.gmt_modified = current_timestamp;\n" +
//                        "    return new;\n" +
//                        "end\n" +
//                        "$$\n" +
//                        "language plpgsql;";
//                jdbcTemplate.execute(functionSql);
//                TODO:貌似pgsql的创建trigger也不支持，后面再说吧
//                for (String tableName : tableNames) {
//                    createPostgreSqlTableAndIndex(tableName);
//                }
//                break;
            case "oracle.jdbc.OracleDriver":
                for (String tableName : tableNames) {
                    createOracleTableAndIndex(tableName);
                }
                break;
            case "org.h2.Driver":
                for (String tableName : tableNames) {
                    createH2TableAndIndex(tableName);
                }
                break;
            default:
                break;
        }
    }

    private void createMysqlTableAndIndex(String tableName) throws FileNotFoundException {
        String checkTable = "show tables like '" + tableName + "'";
        List tableList = jdbcTemplate.queryForList(checkTable);
        logger.debug(GsonUtil.toJson(tableList, false));
        /*表不存在时就创建表*/
        if (tableList.size() == 0) {
            String sqlList = ResourceUtil.readUtf8Str("tables/mysql/" + tableName + ".sql");
            runSqlForCreate(sqlList);
        }
    }

    private void createPostgreSqlTableAndIndex(String tableName) {
        String checkTable = " select tablename from pg_tables where schemaname = 'public' and tablename='" + tableName + "'";
        List tableList = jdbcTemplate.queryForList(checkTable);
        logger.debug(GsonUtil.toJson(tableList, false));
        /*表不存在时就创建表*/
        if (tableList.size() == 0) {
            String sqlList = ResourceUtil.readUtf8Str("tables/postgresql/" + tableName + ".sql");
            runSqlForCreate(sqlList);
        }
    }

    private void createOracleTableAndIndex(String tableName) throws FileNotFoundException {
        String userName = env.getProperty("spring.datasource.username");
        String checkTable = "select table_name from all_tables where owner='" + userName.toUpperCase() + "' and table_name = '" + tableName.toUpperCase() + "'";
        List tableList = jdbcTemplate.queryForList(checkTable);
        logger.debug(GsonUtil.toJson(tableList, false));
        /*表不存在时就创建表*/
        if (tableList.size() == 0) {
            String sqlList = ResourceUtil.readUtf8Str("tables/oracle/" + tableName + ".sql");
            runSqlForCreate(sqlList);
            if (!"mdms_ver".equals(tableName)) {
                //oracle的trigger要单独建
                String tiggerSql = "create or replace trigger " + tableName + "\n" +
                        "  before update on " + tableName + "\n" +
                        "  for each row\n" +
                        "  begin\n" +
                        "     :new.gmt_modified :=sysdate;\n" +
                        "end " + tableName + ";";
                jdbcTemplate.execute(tiggerSql);
            }
        }
    }

    private void createH2TableAndIndex(String tableName) throws IOException {
        String checkTable = "select table_name from information_schema.tables where table_schema='PUBLIC' and table_name = '" + tableName.toUpperCase() + "'";
        List tableList = jdbcTemplate.queryForList(checkTable);
        logger.debug(GsonUtil.toJson(tableList, false));

        /*表不存在时就创建表*/
        if (tableList.size() == 0) {
            String result = ResourceUtil.readUtf8Str("tables/h2/" + tableName + ".sql");
            runSqlForCreate(result);
        }

    }


    private void runSqlForCreate(String sqlList) {
        logger.debug("sqlList:" + sqlList);
        StringTokenizer stn = new StringTokenizer(sqlList, ";");
        while (stn.hasMoreTokens()) {
            String sql = stn.nextToken();
            sql = sql.trim().replaceAll("\r", "").replaceAll("\n", "");
            if (sql.toLowerCase().startsWith("create") || sql.toLowerCase().startsWith("insert")) {
                jdbcTemplate.execute(sql);
            }
        }
    }
}
